package com.blueocean_health.care.management.service.impl;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.filefilter.EmptyFileFilter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.blueocean_health.care.management.common.base.em.TimeType;
import com.blueocean_health.care.management.common.base.id.IdUtils;
import com.blueocean_health.care.management.common.base.image.ImageUtil;
import com.blueocean_health.care.management.common.base.pdf.nurse.NursePdfUtils;
import com.blueocean_health.care.management.common.base.pinyin.Chinese2PinyinUtils;
import com.blueocean_health.care.management.common.base.string.StringUtils;
import com.blueocean_health.care.management.common.base.zip.ZipUtils;
import com.blueocean_health.care.management.common.excel.ExcelUtil;
import com.blueocean_health.care.management.domain.base.NurseGroupRelation;
import com.blueocean_health.care.management.domain.base.NurseWorkerDetailInfo;
import com.blueocean_health.care.management.domain.base.NurseWorkerInfo;
import com.blueocean_health.care.management.domain.base.Param;
import com.blueocean_health.care.management.domain.base.ParamItem;
import com.blueocean_health.care.management.domain.base.SysResult;
import com.blueocean_health.care.management.domain.base.SysResultByPage;
import com.blueocean_health.care.management.domain.dto.NurseWorkerInfoDto;
import com.blueocean_health.care.management.domain.dto.NurseWorkerInfoHealthCertResponseDto;
import com.blueocean_health.care.management.domain.dto.NurseWorkerInfoRequestDto;
import com.blueocean_health.care.management.domain.dto.NurseWorkerInfoResponseDto;
import com.blueocean_health.care.management.domain.dto.NurseWorkerListDto;
import com.blueocean_health.care.management.domain.dto.PullDownDto;
import com.blueocean_health.care.management.domain.ordervo.Criteria;
import com.blueocean_health.care.management.domain.ordervo.OrderServiceNurseInfoVo;
import com.blueocean_health.care.management.domain.worktablevo.WorkersVo;
import com.blueocean_health.care.management.em.NurseImageSizeEnum;
import com.blueocean_health.care.management.em.NurseWorkStatusEnum;
import com.blueocean_health.care.management.em.WorkStatusNewEnum;
import com.blueocean_health.care.management.em.WorkerTypeEnum;
import com.blueocean_health.care.management.exception.OrderServiceException;
import com.blueocean_health.care.management.mapper.NurseGroupMapper;
import com.blueocean_health.care.management.mapper.NurseWorkerDetailInfoMapper;
import com.blueocean_health.care.management.mapper.NurseWorkerInfoMapper;
import com.blueocean_health.care.management.mapper.OrderServiceNurseInfoMapper;
import com.blueocean_health.care.management.service.IParamService;
import com.blueocean_health.care.management.service.NurseWorkerInfoService;
import com.blueocean_health.care.management.util.FileTypeUtil;
import com.blueocean_health.care.management.util.NurseMessageUtils;
import com.blueocean_health.care.management.util.PicUtils;
import com.blueocean_health.care.management.util.TimeUtils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

/**
 * 
 * @author luxue
 *
 */
@Service
public class NurseWorkerInfoServiceImpl implements NurseWorkerInfoService {
	private final static String xls = "xls";
	private final static String xlsx = "xlsx";

	@Value("${picture.filePath.nurse}")
	private String PICTUREPATH;
	@Value("${picture.filePath.nurse.pdf}")
	private String PDFPICTUREPATH;
	@Value("${service.address.nurse}")
	private String serviceAddress;
	@Value("${service.address.nurse.pdf}")
	private String pdfServiceAddress;
	/** 最大服务人数 */
	@Value("${work.service.max}")
	private Integer workServiceMax;

	@Autowired
	private NurseWorkerInfoMapper nurseWorkerInfoMapper;
	@Autowired
	private NurseWorkerDetailInfoMapper nurseWorkerDetailInfoMapper;
	@Autowired
	private OrderServiceNurseInfoMapper orderServiceNurseInfoMapper;
	@Autowired
	private NurseGroupMapper nurseGroupMapper;
	@Autowired
	private IParamService paramService;

	@Transactional
	@Override
	public SysResult add(NurseWorkerInfoDto nurseWorkerInfoDto) {
		NurseWorkerInfo mainBean = new NurseWorkerInfo();
		NurseWorkerDetailInfo detailBean = new NurseWorkerDetailInfo();
		// 去除所有空格
		String workerName = nurseWorkerInfoDto.getWorkerName().replaceAll("\\s*", "");
		mainBean.setWorkerName(workerName);
		try {
			BeanUtils.copyProperties(mainBean, nurseWorkerInfoDto);
			BeanUtils.copyProperties(detailBean, nurseWorkerInfoDto);
		} catch (IllegalAccessException | InvocationTargetException e) {
		}
		// 校验护工编号唯一性
		String workerId = mainBean.getWorkerId();
		NurseWorkerInfoDto checkInfo = nurseWorkerInfoMapper.selectByWorkerId(workerId);
		if (!StringUtils.isEmpty(checkInfo)) {
			return SysResult.error(workerId + "：护工编号已存在，请修改后提交。", mainBean);
		}
		// 校验护工身份证号唯一性 2018年5月2日11:08:19
		int count = nurseWorkerDetailInfoMapper.checkIdCardUnique(nurseWorkerInfoDto);
		if (count > 0) {
			return SysResult.error("身份证重复");
		}

		// 设置护工编号 还原成一期，护工编号还是用户设置
		mainBean.setWorkerId(IdUtils.getRandomIdByMillis());
		detailBean.setWorkerId(mainBean.getWorkerId());
		// 设置创建时间
		detailBean.setCreateTime(new Date());
		
		if (!StringUtils.isEmpty(detailBean.getNativePlace()) || detailBean.getNativePlace().length > 0) {
			// 省 省代码 年龄
			String[] nativePlace = detailBean.getNativePlace();
			// 省份
			mainBean.setProvince(nativePlace[0]);
		}
		
		// 年龄
		int age = (int) (TimeUtils.getYear(new Date()) - TimeUtils.getYear(detailBean.getBirthDate()));
		mainBean.setAge(age);
		// 处理图片地址
		dealPicturUrl(detailBean);
		// 护工姓名拼音
		mainBean.setWorkerNamePinYin(Chinese2PinyinUtils.toPinyinAll(mainBean.getWorkerName()));
		// 提醒状态
		detailBean.setRemindStatus(false);

		// 插入数据
		nurseWorkerInfoMapper.insertSelective(mainBean);
		nurseWorkerDetailInfoMapper.insertSelective(detailBean);
		return SysResult.ok();
	}

	/**
	 * 修改护工基本信息
	 */
	@Transactional
	@Override
	public SysResult modify(NurseWorkerInfoDto nurseWorkerInfoDto) {
		// 校验护工编号唯一性
		NurseWorkerInfo info = nurseWorkerInfoMapper.selectByPrimaryKey(nurseWorkerInfoDto.getId());
		NurseWorkerDetailInfo detailInfo = nurseWorkerDetailInfoMapper.selectByPrimaryKey(info.getWorkerId());
		if (StringUtils.isEmpty(info)) {
			return SysResult.error("id：" + nurseWorkerInfoDto.getId() + "不存在");
		}
		NurseWorkerInfo mainBean = new NurseWorkerInfo();
		NurseWorkerDetailInfo detailBean = new NurseWorkerDetailInfo();
		try {
			BeanUtils.copyProperties(mainBean, nurseWorkerInfoDto);
			BeanUtils.copyProperties(detailBean, nurseWorkerInfoDto);
		} catch (IllegalAccessException | InvocationTargetException e) {
		}
		// 编辑之后工作状态为已离职，判断是否在小组中
		if (NurseWorkStatusEnum._4.getKey().equals(nurseWorkerInfoDto.getWorkStatus())) {
			NurseGroupRelation nurseGroupRelation = nurseGroupMapper.selectByWorkerId(nurseWorkerInfoDto.getWorkerId());
			// 是某个小组的成员
			if (!StringUtils.isEmpty(nurseGroupRelation)) {
				return SysResult.error("请先将该护工移出所在小组，再进行状态变更操作");
			}
		}
		
		if (!StringUtils.isEmpty(detailBean.getNativePlace()) || detailBean.getNativePlace().length > 0) {
			// 省 省代码 年龄
			String[] nativePlace = detailBean.getNativePlace();
			// 省份
			mainBean.setProvince(nativePlace[0]);
		}
		
		// 年龄
		int age = (int) (TimeUtils.getYear(new Date()) - TimeUtils.getYear(detailBean.getBirthDate()));
		mainBean.setAge(age);
		// 处理图片地址--只保存图片名称
		dealPicturUrl(detailBean);
		// 护工姓名拼音
		mainBean.setWorkerNamePinYin(Chinese2PinyinUtils.toPinyinAll(mainBean.getWorkerName()));
		// 创建时间
		detailBean.setCreateTime(detailInfo.getCreateTime());
		// 提醒状态
		detailBean.setRemindStatus(detailInfo.getRemindStatus());
		// 是否有健康证
		if (detailBean.getHaveHealthCert() == false) {
			// 健康证有效期结束时间
			detailBean.setHealthCertExpiryDate(null);
			// 健康证号
			detailBean.setHealthCert("");
			// 健康证照片url
			detailBean.setHealthCertPhotoUrl("");
			// 重置健康证有效期结束时间接收参数
			nurseWorkerInfoDto.setHealthCertExpiryDate(null);
		}
		// 判断是否修改健康证到期日
		if (isModifyHealthCertExpiryDate(detailInfo, nurseWorkerInfoDto)) {
			// 修改提醒状态
			detailBean.setRemindStatus(false);
		}
		// 校验护工身份证号唯一性 2018年4月19日10:13:20
		int count = nurseWorkerDetailInfoMapper.checkIdCardUnique(nurseWorkerInfoDto);
		if (count > 0) {
			return SysResult.error("身份证重复");
		}

		// 更新数据
		nurseWorkerInfoMapper.updateByPrimaryKey(mainBean);
		nurseWorkerDetailInfoMapper.updateByPrimaryKey(detailBean);
		return SysResult.ok();
	}

	/**
	 * 删除护工
	 */
	@Transactional
	@Override
	public SysResult delete(Integer id) {
		NurseWorkerInfo info = nurseWorkerInfoMapper.selectByPrimaryKey(id);
		if (StringUtils.isEmpty(info)) {
			return SysResult.error("参数有误，请重新输入参数。");
		}
		// 校验订单中是否有护工以及小组中是否有护工
		if (orderServiceNurseInfoMapper.getMaxVersionWorkerIdNumber(info.getWorkerId()) > 0) {
			return SysResult.error("该护工有关联订单，无法删除");
		}
		if (nurseGroupMapper.getWorkerIdGroupNumber(info.getWorkerId()) > 0) {
			return SysResult.error("请先在小组中删除" + info.getWorkerName());
		}
		NurseWorkerInfo record = new NurseWorkerInfo();
		record.setId(id);
		record.setIsDel(true);
		int count = nurseWorkerInfoMapper.updateIsdelById(record);
		if (count == 0) {
			return SysResult.error("删除失败");
		} else {
			return SysResult.ok("删除成功");
		}
	}

	/**
	 * 根据护工Id获取护工详情
	 * 
	 * @param id 护工表id
	 */
	@Override
	public SysResult findDetail(Integer id) {
		NurseWorkerInfoDto nurseWorkerInfo = nurseWorkerInfoMapper.selectById(id);
		if (StringUtils.isEmpty(nurseWorkerInfo)) {
			return SysResult.error("id：" + id + "对应的护工信息不存在！");
		}
		setUrl(nurseWorkerInfo); // 重置URL
		return SysResult.ok(nurseWorkerInfo);
	}

	/**
	 * 根据护工workerId获取护工详情
	 * 
	 * @param workerId 护工编号
	 */
	@Override
	public SysResult getNurseWorkerInfoById(String workerId) {
		NurseWorkerInfoDto nurseWorker = nurseWorkerInfoMapper.selectByWorkerId(workerId);
		if (StringUtils.isEmpty(nurseWorker)) {
			return SysResult.error("对应的护工信息不存在");
		}
		setUrl(nurseWorker); // 重置URL
		return SysResult.ok(nurseWorker);
	}

	/**
	 * 分页查询护工列表
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public SysResult list(NurseWorkerInfoRequestDto nurseWorkerInfoRequestDto) {
		// 设置分页
		PageHelper.startPage(nurseWorkerInfoRequestDto.getPage(), nurseWorkerInfoRequestDto.getPageSize());
		// 条件查询护工
		List<NurseWorkerInfoResponseDto> nurseWorkerInfoPageDtoList = nurseWorkerInfoMapper
				.list(nurseWorkerInfoRequestDto, nurseWorkerInfoRequestDto.getTeamId());
		
		PageInfo<NurseWorkerInfoResponseDto> pageInfo = new PageInfo(nurseWorkerInfoPageDtoList);
		return SysResultByPage.ok(0, "SUCCESS", pageInfo.getList(), pageInfo.getTotal());
	}

	/**
	 * 导出护工Execl
	 */
	@Override
	public List<NurseWorkerInfoResponseDto> listNurseWorkerInfoExcel(
			NurseWorkerInfoRequestDto nurseWorkerInfoRequestDto) {
		return nurseWorkerInfoMapper.list(nurseWorkerInfoRequestDto, nurseWorkerInfoRequestDto.getTeamId());
	}

	/**
	 * 根据筛选条件获取护工列表 2017年10月20日15:34:23
	 */
	@Override
	public List<PullDownDto> getNurseList(Map<String, String> map) {
		String workerName = map.get("workerName");
		if (StringUtils.isEmpty(workerName.trim())) {
			return null;
		}
		List<PullDownDto> nurseList = nurseWorkerInfoMapper.getNurseList(map);
		return nurseList;
	}

	/**
	 * 根据护工姓名或手机号获取护工列表
	 * 
	 * @throws OrderServiceException
	 */
	@Override
	public PageInfo<NurseWorkerListDto> getNurseWorkerListNew(Map<String, String> map) throws OrderServiceException {
		// 1.查询当前页的护工数据
		String teamId = map.get("hospitalId");
		List<NurseWorkerListDto> nurseWorkerList = nurseWorkerInfoMapper.getNurseWorkerList(map);
		// 修改头像的url
		nurseWorkerList.stream().forEach(n -> {
			if (!StringUtils.isEmpty(n.getAvatarPhotoUrl())) {
				n.setAvatarPhotoUrl(serviceAddress + n.getAvatarPhotoUrl());
			}
		});

		// 2.获取所有的工作中的护工列表
		String startTime = TimeUtils.getTime(), endTime = TimeUtils.getTime();
		// （包含退款信息，但是没有进行垃圾数据过滤）
		List<OrderServiceNurseInfoVo> workingNurseList = orderServiceNurseInfoMapper.selectNurseByWorking(teamId,
				startTime, endTime);
		// 对含有退款时间的数据和垃圾数据（老version的数据）进行处理
		List<OrderServiceNurseInfoVo> workingNurseListAndRefund = new ArrayList<>();
		Long paramStart = TimeUtils.getDateByString(startTime, TimeType.yyyy_MM_dd_HH_mm_ss).getTime();
		Long paramEnd = TimeUtils.getDateByString(endTime, TimeType.yyyy_MM_dd_HH_mm_ss).getTime();

		workingNurseList.forEach(info -> {
			if (info.getRefundStart() != null && info.getRefundEnd() != null) {
				// 退前面
				if (info.getRefundStart().getTime() == info.getStartTime().getTime()
						&& info.getRefundEnd().getTime() != info.getEndTime().getTime()) {
					if (paramEnd > info.getRefundEnd().getTime()) {
						workingNurseListAndRefund.add(info);
					}
				} else if (info.getRefundStart().getTime() != info.getStartTime().getTime()
						&& info.getRefundEnd().getTime() == info.getEndTime().getTime()) {
					// 退后面
					if (paramStart < info.getRefundStart().getTime()) {
						workingNurseListAndRefund.add(info);
					}
				} else if (info.getRefundStart().getTime() != info.getStartTime().getTime()
						&& info.getRefundEnd().getTime() != info.getEndTime().getTime()) {
					// 退中间
					if (paramStart < info.getRefundStart().getTime() || paramEnd > info.getRefundEnd().getTime()) {
						workingNurseListAndRefund.add(info);
					}
				} else if (info.getRefundStart().getTime() == info.getStartTime().getTime()
						&& info.getRefundEnd().getTime() == info.getEndTime().getTime()) {
					// 全退，排除
				} else {

				}
			} else {
				// 退款信息是空的时候
				workingNurseListAndRefund.add(info);
			}

		});

		Map<String, List<OrderServiceNurseInfoVo>> workingMap = new HashMap<>();
		workingNurseListAndRefund.forEach(nurseInfo -> {
			List<OrderServiceNurseInfoVo> tempList = null;
			if (workingMap.containsKey(nurseInfo.getWorkerId())) {
				tempList = workingMap.get(nurseInfo.getWorkerId());
			} else {
				tempList = new ArrayList<>();
				workingMap.put(nurseInfo.getWorkerId(), tempList);
			}
			tempList.add(nurseInfo);
		});
		// 3.获取每个护工所有最新版本的订单明细中，结束时间最晚的集合
		List<OrderServiceNurseInfoVo> allMaxVersionEndtime = orderServiceNurseInfoMapper
				.getAllMaxVersionEndtimeNew(teamId);
		allMaxVersionEndtime.forEach(nurseInfo -> {
			if (nurseInfo.getRefundStart() != null && nurseInfo.getRefundEnd() != null) {
				// 本条明细全退，排除这条的时间
				if (nurseInfo.getRefundStart().getTime() == nurseInfo.getStartTime().getTime()
						&& nurseInfo.getRefundEnd().getTime() == nurseInfo.getEndTime().getTime()) {
					nurseInfo.setStartTime(null);
				} else if (nurseInfo.getRefundStart().getTime() == nurseInfo.getStartTime().getTime()
						&& nurseInfo.getRefundEnd().getTime() != nurseInfo.getEndTime().getTime()) {
					// 退前面，结束时间不受影响
				} else if (nurseInfo.getRefundStart().getTime() != nurseInfo.getStartTime().getTime()
						&& nurseInfo.getRefundEnd().getTime() == nurseInfo.getEndTime().getTime()) {
					// 退后面，结束时间就是退款的开始时间
					nurseInfo.setEndTime(nurseInfo.getRefundStart());
				} else if (nurseInfo.getRefundStart().getTime() != nurseInfo.getStartTime().getTime()
						&& nurseInfo.getRefundEnd().getTime() != nurseInfo.getEndTime().getTime()) {
					// 退中间，结束时间不受影响
				}
			}
		});

		// 找打最晚的那个结束时间
		Map<String, OrderServiceNurseInfoVo> maxEndTimeMap = new HashMap<>();
		allMaxVersionEndtime.forEach(orderServiceNurseInfo -> {
			if (orderServiceNurseInfo.getStartTime() != null) {
				// 找出最晚的时间
				OrderServiceNurseInfoVo oldvo = maxEndTimeMap.get(orderServiceNurseInfo.getWorkerId());
				if (oldvo == null) {
					maxEndTimeMap.put(orderServiceNurseInfo.getWorkerId(), orderServiceNurseInfo);
				} else {
					Date oldTime = oldvo.getEndTime();
					Date newTime = orderServiceNurseInfo.getEndTime();
					if (newTime.getTime() >= oldTime.getTime()) {
						maxEndTimeMap.put(orderServiceNurseInfo.getWorkerId(), orderServiceNurseInfo);
					}
				}
			}
		});

		String serviceType = "1";
		for (int i = 0; i < nurseWorkerList.size(); i++) {
			NurseWorkerListDto dto = nurseWorkerList.get(i);
			if (workingMap.containsKey(dto.getWorkerId())) {
				if (workingMap.get(dto.getWorkerId()).size() > 0) {
					serviceType = workingMap.get(dto.getWorkerId()).get(0).getServiceType();
				}

				if ("1".equals(serviceType)) {
					// 一对一服务,
					dto.setWorkStatus(Integer.parseInt(WorkStatusNewEnum._1.getKey()));
				} else if ("2".equals(serviceType)) {
					// 二对一服务,
					dto.setWorkStatus(Integer.parseInt(WorkStatusNewEnum._1.getKey()));
				} else if ("3".equals(serviceType)) {
					// 一对多 服务,
					dto.setWorkStatus(Integer.parseInt(WorkStatusNewEnum._1.getKey()));
				} else if ("5".equals(serviceType)) {
					// 单项服务,
					dto.setWorkStatus(Integer.parseInt(WorkStatusNewEnum._1.getKey()));
				} else {// 对多服务, 精准计算, 按照半个小时的时间单位

					{// 方式二
						if (workingMap.get(dto.getWorkerId()).size() >= workServiceMax) {
							dto.setWorkStatus(Integer.parseInt(WorkStatusNewEnum._1.getKey()));
						}
					}
				}
			} else {
			}
		}

		// 在职待岗的护工显示在最上方，进行排序
		nurseWorkerList.sort((o1, o2) -> {
			return o1.getWorkStatus().compareTo(o2.getWorkStatus());
		});
		List<NurseWorkerListDto> tempList = new ArrayList<>();
		Iterator<NurseWorkerListDto> iterator = nurseWorkerList.iterator();
		while(iterator.hasNext()) {
			NurseWorkerListDto next = iterator.next();
			if(next.getWorkStatus() == 2) {
				tempList.add(next);
				iterator.remove();
			}
		}
		nurseWorkerList.addAll(0, tempList);
		
		// 分页
		int end = nurseWorkerList.size();
		int pageSize = Integer.parseInt(map.get("pageSize") + "");
		int page = Integer.parseInt(map.get("page") + "");
		int first = (page - 1) * pageSize;
		end = (first + pageSize > nurseWorkerList.size() ? nurseWorkerList.size() : first + pageSize);
		List<NurseWorkerListDto> finresList = new ArrayList<>();
		for (int i = first; i < end; i++) {
			finresList.add(nurseWorkerList.get(i));
		}
		PageInfo<NurseWorkerListDto> pageInfo = new PageInfo<NurseWorkerListDto>(finresList);
		return pageInfo;
	}

	/**
	 * 更新护工表中所有护工拼音字段
	 * 
	 * @return
	 */
	@Transactional
	@Override
	public SysResult updateWorkernamePinyin() {
		List<WorkersVo> workers = nurseWorkerInfoMapper.getAllWorkers();
		for (WorkersVo bean : workers) {
			String workerNamePinYin = Chinese2PinyinUtils.toPinyinAll(bean.getWorkerName());
			nurseWorkerInfoMapper.updateWorkernamePinyin(workerNamePinYin, bean.getWorkerId());
		}
		return SysResult.ok();
	}

	/**
	 * 上传护工图片
	 * 
	 * @throws Exception
	 */
	@Transactional
	@Override
	public SysResult upload(MultipartFile imgFile) throws Exception {
		// 服务器存储路径
		String newPath = "";
		try {
			String errorMsg = "";
			if (imgFile.isEmpty()) {
				errorMsg = "上传参数有误，请重新上传。";
			} else {
				String filename = imgFile.getOriginalFilename().toLowerCase();
				String extName = filename.substring(filename.lastIndexOf("."));
				if (!".png".equals(extName) && !".jpg".equals(extName) && !".jpeg".equals(extName)
						&& !".bmp".equals(extName)) {
					errorMsg = "上传参数有误，请上传图片。";
				}
				if (!StringUtils.isEmpty(errorMsg)) {
					return SysResult.error(errorMsg);
				}
				File file = new File(PICTUREPATH);
				if (!file.exists()) {
					file.mkdirs();
				}
				String pictureName = IdUtils.getRandomIdByMillis() + extName;
				newPath = serviceAddress + pictureName;
				imgFile.transferTo(new File(PICTUREPATH + pictureName));
				// 压缩图片
				PicUtils.commpressPicForScale(PICTUREPATH + pictureName, PICTUREPATH + pictureName, 2048, 0.8);
			}
		} catch (Exception e) {
			throw new Exception(); // 前端上传图片只有抛出异常才能显示上传失败
		}
		return SysResult.ok("图片上传成功", newPath);
	}

	/**
	 * 批量导入护工基本信息
	 */
	@Transactional
	@Override
	public SysResult importNurseExcel(String teamId, MultipartFile file) {

		try {
			// 文件名称
			String fileName = file.getOriginalFilename();
			if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) {
				return SysResult.error("文件格式不正确");
			}
			InputStream inputStream = file.getInputStream();
			// 获得Excel对象
			Workbook workbook = ExcelUtil.getInstance().getWorkbook(inputStream, fileName);
			// 查询字典
			Criteria criteria = new Criteria();
			String[] type = { "workStatus", "sex", "province", "nation", "education", "creditType", "bank" };
			criteria.setType(type);
			criteria.setDisplay("1");
			List<Param> params = paramService.getParams(criteria);
			Map<String, Map<String, String>> paramMap = new HashMap<String, Map<String, String>>();
			for (Param param : params) {
				Map<String, String> map = new HashMap<String, String>();
				for (ParamItem item : param.getItems()) {
					map.put(item.getName(), item.getValue());
				}
				paramMap.put(param.getValue(), map);
			}
			// 记录excel中数据异常的行数集合
			Set<Integer> errorNumSet = new HashSet<Integer>();
			// 数据正常
			List<NurseWorkerInfoDto> nurseWorkerInfoList = new LinkedList<NurseWorkerInfoDto>();
			Map<String, Integer> idCardMap = new HashMap<>();
			// excel评接成JSON数据
			execlToList(teamId, nurseWorkerInfoList, workbook, paramMap, errorNumSet, idCardMap);
			// 校验execl表内数据是否异常
			String errorMsg = checkExeclRule(teamId, workbook, paramMap, errorNumSet, idCardMap);
			// 存在数据异常
			if (!StringUtils.isEmpty(errorMsg)) {
				return SysResult.error(errorMsg);
			}
			if (nurseWorkerInfoList.isEmpty()) {
				return SysResult.error("导入内容为空");
			}
			// 将nurseWorkerInfoList保存到数据库中
			for (NurseWorkerInfoDto info : nurseWorkerInfoList) {

				NurseWorkerInfo bean = new NurseWorkerInfo();
				NurseWorkerDetailInfo bean2 = new NurseWorkerDetailInfo();
				try {
					BeanUtils.copyProperties(bean, info);
					BeanUtils.copyProperties(bean2, info);
				} catch (IllegalAccessException | InvocationTargetException e) {
				}

				// // 添加
				nurseWorkerInfoMapper.insert(bean);
				nurseWorkerDetailInfoMapper.insert(bean2);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return SysResult.ok();
	}

	/**
	 * 批量导入护工图片信息
	 */
	@Transactional
	@Override
	public SysResult importNursePictureZip(String teamId, MultipartFile file) {

		try {
			// 文件名称
			String filename = file.getOriginalFilename();
			// 获取文件后缀
			String extName = filename.substring(filename.lastIndexOf("."));
			if (!".zip".equals(extName)) {
				return SysResult.error("请上传后缀名为zip的压缩包");
			}
			FileInputStream inputStream = (FileInputStream) file.getInputStream();
			FileInputStream copyInputStream = (FileInputStream) file.getInputStream();
			String fileType = FileTypeUtil.getFileType(copyInputStream);
			if (!"zip".equals(fileType)) {
				return SysResult.error("文件格式错误");
			}
			// 解压路径
			String uuidPath = PICTUREPATH + IdUtils.getRandomIdByMillis();
			// 解压文件
			String entryName = "";
			try {
				entryName = ZipUtils.decompressionZip(inputStream, uuidPath);
			} catch (Exception e) {
				return SysResult.error("文件解压失败");
			}
			// 遍历文件夹
			String path = uuidPath + "/";
			// 遍历文件夹
			if (entryName.indexOf("/") > 0) {
				path += entryName.substring(0, entryName.indexOf("/"));
			}
			Collection<File> listFiles = new LinkedList<File>();
			listFiles = FileUtils.listFiles(new File(path), EmptyFileFilter.NOT_EMPTY, null);
			Map<String, Set<String>> errorMap = new HashMap<String, Set<String>>();
			// 存放满足条件的照片 {TX_WZX_0001={head=TX_WZX_0001_head.png, bank=TX_WZX_0001_bank.png,
			// educ=TX_WZX_0001_educ.png, face=TX_WZX_0001_face.png,
			// heal=TX_WZX_0001_heal.png, back=TX_WZX_0001_back.png}}
			Map<String, Map<String, String>> pictureMap = new HashMap<String, Map<String, String>>();
			String[] ruleName = { "_head.", "_bank.", "_educ.", "_heal.", "_face.", "_back." };
			// 存放处理过后的pdf图片路径
			String pdfPath = path + "/" + IdUtils.getRandomIdByUUID();
			File pdfFile = new File(pdfPath);
			pdfFile.mkdirs();
			// 需要处理成图片size
			NurseImageSizeEnum sizeEnum = null;
			for (File f : listFiles) {
				String fileName = f.getName();
				String fileExtName = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase(); // png jpg
				// 图片命名不对
				if (!StringUtils.endsWithStr(ruleName, fileName.substring(0, fileName.lastIndexOf(".") + 1))) {
					// 存放错误信息
					setErrorMap(errorMap, "name", fileName);
				}
				// 上传图片文件格式不对
				else if ((!"png".equals(fileExtName) && !"jpg".equals(fileExtName) && !"jpeg".equals(fileExtName)
						&& !"bmp".equals(fileExtName))) {
					// 存放错误信息
					setErrorMap(errorMap, "format", fileName);
				} else {
					String workerId = fileName.substring(0, fileName.lastIndexOf("_")); // TX_WZX_0001
					String identification = fileName.substring(fileName.lastIndexOf("_") + 1,
							fileName.lastIndexOf(".")); // head
					// 查看是否有对应护工编号的员工
					// NurseWorkerInfo info = nurseWorkerInfoMapper.selectByWorkerId(workerId);
					NurseWorkerInfoDto infoDto = nurseWorkerInfoMapper.selectByWorkerId(workerId);
					// 护工基本信息不存在
					if (StringUtils.isEmpty(infoDto)) {
						// 存放错误信息 workerId
						setErrorMap(errorMap, "exist", fileName);
					}
					// 判断当前登录人是否有权限编辑
					else if (!StringUtils.isEmpty(infoDto) && !teamId.equals(infoDto.getHospital())) {
						// 存放错误信息 workerId
						setErrorMap(errorMap, "other", fileName);
					}
					// 满足条件的图片
					else {
						try {
							sizeEnum = NurseImageSizeEnum.valueOf(identification.toUpperCase());
							try {
								// 重调图片尺寸
								ImageUtil.resize(path + "/" + fileName, pdfPath + "/" + fileName, sizeEnum.getWidth(),
										sizeEnum.getHeight());
							} catch (Exception e) {
							}
						} catch (IllegalArgumentException e) {
						}
						// 压缩图片
						PicUtils.commpressPicForScale(path + "/" + fileName, path + "/" + fileName, 500, 0.8);
						if (!pictureMap.containsKey(workerId)) {
							Map<String, String> myMap = new HashMap<String, String>();
							myMap.put(identification, fileName);
							pictureMap.put(workerId, myMap);
						} else {
							Map<String, String> myMap = pictureMap.get(workerId);
							myMap.put(identification, fileName);
							pictureMap.put(workerId, myMap);
						}
					}
				}
			}
			// 符合条件护工集合
			List<NurseWorkerInfoDto> updateNurseList = new LinkedList<NurseWorkerInfoDto>();
			// 循环pictureMap
			for (String workerId : pictureMap.keySet()) {
				NurseWorkerInfoDto temp = new NurseWorkerInfoDto();
				temp.setWorkerId(workerId);
				temp.setAvatarPhotoUrl(pictureMap.get(workerId).get("head")); // 头像地址
				temp.setBankCardPhotoUrl(pictureMap.get(workerId).get("bank")); // 银行卡地址
				temp.setEducationPhotoUrl(pictureMap.get(workerId).get("educ")); // 学历证地址
				temp.setHealthCertPhotoUrl(pictureMap.get(workerId).get("heal")); // 健康证地址
				temp.setIdCardFacePhotoUrl(pictureMap.get(workerId).get("face")); // 身份证正面地址
				temp.setIdCardBackPhotoUrl(pictureMap.get(workerId).get("back")); // 身份证反面地址
				updateNurseList.add(temp);
			}

			if (!errorMap.isEmpty()) {
				// 删除文件
				FileUtils.deleteDirectory(new File(uuidPath));
				String errorMsg = setErrorMsg(errorMap);
				return SysResult.error(errorMsg.substring(0, errorMsg.length() - 1));
			} else {
				// 更新到数据库 -- updateNurseList
				for (NurseWorkerInfoDto n : updateNurseList) {
					nurseWorkerDetailInfoMapper.modifyPicture(n);
					// nurseWorkerInfoMapper.modifyPicture(n);
				}
				// 复制处理后的图片
				FileUtils.copyDirectory(new File(pdfPath), new File(PDFPICTUREPATH), false);
				// 删除处理后的图片
				FileUtils.deleteDirectory(new File(pdfPath));
				// 复制原图片文件
				FileUtils.copyDirectory(new File(path), new File(PICTUREPATH), false);
				// 删除文件
				FileUtils.deleteDirectory(new File(uuidPath));
			}

		} catch (IOException e) {
			return SysResult.error("操作失败");
		}
		return SysResult.ok();

	}

	/**
	 * 健康证延期提醒列表
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public SysResult listHealthCert(NurseWorkerInfoRequestDto nurseWorkerInfoRequestDto) {
		// 设置分页
		PageHelper.startPage(nurseWorkerInfoRequestDto.getPage(), nurseWorkerInfoRequestDto.getPageSize());
		// 条件查询护工
		List<NurseWorkerInfoHealthCertResponseDto> nurseWorkerInfoPageDtoList = nurseWorkerInfoMapper
				.listHealthCert(nurseWorkerInfoRequestDto, nurseWorkerInfoRequestDto.getTeamId());
		PageInfo<NurseWorkerInfoHealthCertResponseDto> pageInfo = new PageInfo(nurseWorkerInfoPageDtoList);
		return SysResultByPage.ok(0, "SUCCESS", pageInfo.getList(), pageInfo.getTotal());
	}

	/**
	 * 健康证延期提醒--导出Excel
	 */
	@Override
	public List<NurseWorkerInfoHealthCertResponseDto> exportHealthCertExcel(
			NurseWorkerInfoRequestDto nurseWorkerInfoRequestDto) {

		// 条件查询护工
		List<NurseWorkerInfoHealthCertResponseDto> nurseWorkerInfoPageDtoList = nurseWorkerInfoMapper
				.listHealthCert(nurseWorkerInfoRequestDto, nurseWorkerInfoRequestDto.getTeamId());
		return nurseWorkerInfoPageDtoList;
	}

	/**
	 * 修改到期日
	 */
	@Override
	public SysResult modifyHealthDate(NurseWorkerInfoDto nurseWorkerInfoDto) {
		Integer id = nurseWorkerInfoDto.getId();
		if (StringUtils.isEmpty(nurseWorkerInfoDto.getHealthCertExpiryDate(), id, nurseWorkerInfoDto.getHealthCert())) {
			return SysResult.error("参数有误，请重新输入参数。");
		}
		// 查询护工信息
		NurseWorkerInfo workerInfo = nurseWorkerInfoMapper.selectByPrimaryKey(nurseWorkerInfoDto.getId());
		NurseWorkerDetailInfo detailInfo = nurseWorkerDetailInfoMapper.selectByPrimaryKey(workerInfo.getWorkerId());
		// 判断是否修改健康证到期日
		if (isModifyHealthCertExpiryDate(detailInfo, nurseWorkerInfoDto)) {
			// 修改提醒状态
			detailInfo.setRemindStatus(false);
		}
		detailInfo.setHaveHealthCert(true);
		detailInfo.setHealthCert(nurseWorkerInfoDto.getHealthCert());
		detailInfo.setHealthCertExpiryDate(nurseWorkerInfoDto.getHealthCertExpiryDate());
		int count = nurseWorkerDetailInfoMapper.updateByPrimaryKeySelective(detailInfo);
		if (count > 0) {
			return SysResult.ok();
		}
		return SysResult.error("数据不存在");
	}

	/**
	 * 提醒补办
	 */
	@Override
	@Transactional
	public SysResult remind(NurseWorkerInfoDto nurseWorkerInfoDto) {
		NurseWorkerInfoHealthCertResponseDto bean = nurseWorkerInfoMapper
				.selectHealthCertByIdAndHospitalId(nurseWorkerInfoDto, nurseWorkerInfoDto.getHospital());
		if (StringUtils.isEmpty(bean)) {
			return SysResult.error("参数有误");
		}
		try {
			// 发送短信
			NurseMessageUtils.sendSingleMessage(bean);
			// 更新提醒状态
			nurseWorkerDetailInfoMapper.updateRemindStatus(bean, nurseWorkerInfoDto.getHospital());
		} catch (Exception e) {
			return SysResult.error(e.getMessage());
		}
		return SysResult.ok();
	}

	/**
	 * 批量提醒
	 * 
	 * @param nurseWorkerInfoRequestDto
	 * @return
	 */
	@Override
	@Transactional
	public SysResult batchRemind(NurseWorkerInfoRequestDto nurseWorkerInfoRequestDto) {
		List<NurseWorkerInfoHealthCertResponseDto> list = nurseWorkerInfoMapper
				.listHealthCert(nurseWorkerInfoRequestDto, nurseWorkerInfoRequestDto.getTeamId());
		if (list.isEmpty()) {
			return SysResult.error("未查询到符合条件的护工");
		}
		try {
			// 发送短信
			NurseMessageUtils.sendBatchMessage(list);
			nurseWorkerDetailInfoMapper.batchUpdateRemindStatus(list, nurseWorkerInfoRequestDto.getTeamId());
		} catch (Exception e) {
			return SysResult.error(e.getMessage());
		}
		return SysResult.ok();
	}

	/**
	 * 下载文档pdf
	 */
	@Override
	public void downloadPdf(String teamId, Integer[] ids, HttpServletResponse response) {
		OutputStream toClient = null;
		try {
			String finalFileName = URLEncoder.encode("护理员档案.pdf", "UTF8");
			response.addHeader("Content-Disposition", "attachment;filename=" + finalFileName);
			toClient = new BufferedOutputStream(response.getOutputStream());
			List<NurseWorkerInfoDto> infoList = nurseWorkerInfoMapper.selectNurseListByIdsAndHospitalId(ids, teamId);
			if (infoList.isEmpty()) {
				throw new Exception("参数有误，请重新输入参数。");
			}
			NursePdfUtils.exportNurseBaseMessagePdf(toClient, infoList, pdfServiceAddress, PDFPICTUREPATH);
		} catch (Exception e) {
		} finally {
			try {
				if (!StringUtils.isEmpty(toClient)) {
					toClient.flush();
					toClient.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	//////////////////////////////////////////////////////////////////

	/**
	 * 批量导入图片错误消息存放
	 * 
	 * @param errorMap
	 * @param name     错误消息种类
	 * @param msg      消息内容
	 */
	private void setErrorMap(Map<String, Set<String>> errorMap, String name, String msg) {
		if (errorMap.containsKey(name)) {
			Set<String> set = errorMap.get(name);
			set.add(msg);
			errorMap.put(name, set);
		} else {
			Set<String> set = new HashSet<String>();
			set.add(msg);
			errorMap.put(name, set);
		}
	}

	/**
	 * 拼接错误消息
	 * 
	 * @param errorMap 错误消息Map
	 * @return 字符串类型错误消息
	 */
	private String setErrorMsg(Map<String, Set<String>> errorMap) {
		String errorMsg = "";
		if (errorMap.containsKey("name")) {
			errorMsg += errorMap.get("name") + "不符合命名规则，";
		}
		if (errorMap.containsKey("format")) {
			errorMsg += errorMap.get("format") + "非图片格式，";
		}
		if (errorMap.containsKey("exist")) {
			errorMsg += errorMap.get("exist") + "护工编号不存在，";
		}
		if (errorMap.containsKey("other")) {
			errorMsg += errorMap.get("other") + "其他医院护工，";
		}
		return errorMsg;
	}

	/**
	 * 查找sheet页中第一行为空的数据
	 */
	private List<Integer> queryNullLine(Sheet sheet) {
		List<Integer> isNullLine = new ArrayList<Integer>();
		int lastRowNum = sheet.getLastRowNum();
		for (int i = 0; i <= lastRowNum; i++) {
			boolean falg = true;
			Row row = sheet.getRow(i);
			// 循环总列数
			for (int j = 0; j <= 12; j++) {
				try {
					if (!StringUtils.isEmpty(getCellValue(row.getCell(j)))) {
						falg = false; // 一行中存在不为空的单元格
						break;
					}
				} catch (Exception e) {
				}
			}
			if (falg) {
				isNullLine.add(i);
				break;
			}
		}
		return isNullLine;
	}

	/**
	 * 获取单元格内容
	 * 
	 * @param c
	 * @return
	 */
	@SuppressWarnings("deprecation")
	private String getCellValue(Cell c) {
		String o = null;
		if (StringUtils.isEmpty(c)) {
			return o;
		}
		switch (c.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			o = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			o = String.valueOf(c.getBooleanCellValue()).trim();
			break;
		case Cell.CELL_TYPE_FORMULA:
			o = String.valueOf(c.getCellFormula()).trim();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			o = new BigDecimal(String.valueOf(c.getNumericCellValue()).trim()).toPlainString();
			break; // 此处只针对于护工导入处理
		case Cell.CELL_TYPE_STRING:
			o = c.getStringCellValue().trim();
			break;
		default:
			o = null;
			break;
		}
		return o;
	}

	/**
	 * 校验execl表中内容是否合法
	 * 
	 * @param idCardMap
	 * @param nurseWorkerInfoList
	 * @return
	 */
	private String checkExeclRule(String teamId, Workbook workbook, Map<String, Map<String, String>> paramMap,
			Set<Integer> errorNumSet, Map<String, Integer> idCardMap) {
		StringBuffer sb = new StringBuffer();
		// 只解析第一个sheet页
		for (int i = 0; i < 1; i++) {
			// 获取当前sheetNum
			Sheet sheet = workbook.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			// 查询sheet一行数据都为空的行数集合
			List<Integer> queryNullLine = queryNullLine(sheet);
			int lastRowNum = sheet.getLastRowNum();
			// 过滤第一次为空的sheet行及以下
			if (!queryNullLine.isEmpty()) {
				lastRowNum = queryNullLine.get(0) - 1;
			}
			// 校验数据
			for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
				if (queryNullLine.contains(rowNum)) {
					continue;
				}
				Row row = sheet.getRow(rowNum);
				// 姓名
				if (StringUtils.isEmpty(getCellValue(row.getCell(0)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第1列数据为空\n");
				}
				// 工作状态
				if (StringUtils.isEmpty(getCellValue(row.getCell(1)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第2列数据为空\n");
				} else {
					// 不在字典表或者为已离职
					if (StringUtils.isEmpty(paramMap.get("workStatus").get(getCellValue(row.getCell(1))))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第2列数据异常\n");
					}
				}
				// 性别
				if (StringUtils.isEmpty(getCellValue(row.getCell(2)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第3列数据为空\n");
				} else {
					if (StringUtils.isEmpty(paramMap.get("sex").get(getCellValue(row.getCell(2))))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第3列数据异常\n");
					}
				}
				// 籍贯
				if (StringUtils.isEmpty(getCellValue(row.getCell(3)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第4列数据为空\n");
				} else {
					if (StringUtils.isEmpty(paramMap.get("province").get(getCellValue(row.getCell(3))))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第4列数据异常\n");
					}
				}
				// 民族
				if (StringUtils.isEmpty(getCellValue(row.getCell(4)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第5列数据为空\n");
				} else {
					if (StringUtils.isEmpty(paramMap.get("nation").get(getCellValue(row.getCell(4))))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第5列数据异常\n");
					}
				}
				// 手机号
				if (StringUtils.isEmpty(getCellValue(row.getCell(5)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第6列数据为空\n");
				} else {
					if (getCellValue(row.getCell(5)).length() > 12 || getCellValue(row.getCell(5)).length() <= 0) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第6列数据异常\n");
					}
				}
				// 身份证号
				if (StringUtils.isEmpty(getCellValue(row.getCell(6)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第7列数据为空\n");
				} else {
					if (!StringUtils.isCard(getCellValue(row.getCell(6)))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第7列数据异常\n");
					} else {
						NurseWorkerInfoDto dto = new NurseWorkerInfoDto();
						dto.setHospital(teamId);
						dto.setIdCard(getCellValue(row.getCell(6)));
						// 校验身份证号是否唯一
						int count = nurseWorkerDetailInfoMapper.checkIdCardUnique(dto);
						if (count > 0 || idCardMap.get(dto.getIdCard().toLowerCase()) > 1) {
							errorNumSet.add(rowNum);
							sb.append("第" + (rowNum + 1) + "行第7列身份证重复\n");
						}
					}
				}
				// 出生日期
				if (StringUtils.isEmpty(getCellValue(row.getCell(7)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第8列数据为空\n");
				} else {
					if (TimeUtils.checkDate(getCellValue(row.getCell(7)))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第8列数据异常\n");
					}
				}
				// 入职日期
				if (StringUtils.isEmpty(getCellValue(row.getCell(8)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第9列数据为空\n");
				} else {
					if (TimeUtils.checkDate(getCellValue(row.getCell(8)))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第9列数据异常\n");
					}
				}
				// 学历
				if (StringUtils.isEmpty(getCellValue(row.getCell(9)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第10列数据为空\n");
				} else {
					if (StringUtils.isEmpty(paramMap.get("education").get(getCellValue(row.getCell(9))))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第10列数据异常\n");
					}
				}
				// 开户行
				if (StringUtils.isEmpty(getCellValue(row.getCell(10)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第11列数据为空\n");
				} else {
					if (StringUtils.isEmpty(paramMap.get("bank").get(getCellValue(row.getCell(10))))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第11列数据异常\n");
					}
				}
				// 卡号
				if (StringUtils.isEmpty(getCellValue(row.getCell(11)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第12列数据为空\n");
				} else {
					if (getCellValue(row.getCell(11)).length() > 25) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第12列数据异常\n");
					} else if (!StringUtils.isBankCard(getCellValue(row.getCell(11)))) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第12列数据异常\n");
					}
				}
				// 支行名称
				if (StringUtils.isEmpty(getCellValue(row.getCell(12)))) {
					errorNumSet.add(rowNum);
					sb.append("第" + (rowNum + 1) + "行第13列数据为空\n");
				} else {
					if (getCellValue(row.getCell(12)).length() > 50) {
						errorNumSet.add(rowNum);
						sb.append("第" + (rowNum + 1) + "行第13列数据异常\n");
					}
				}
			}
		}
		return sb.toString();
	}

	/**
	 * 将Execl表中的数据保存到List中
	 * 
	 * @param workbook
	 * @param paramMap
	 * @param idCardMap
	 * @return
	 */
	private void execlToList(String teamId, List<NurseWorkerInfoDto> nurseWorkerInfoList, Workbook workbook,
			Map<String, Map<String, String>> paramMap, Set<Integer> errorNumSet, Map<String, Integer> idCardMap) {
		// 只获取第一个sheet页的数据
		for (int i = 0; i < 1; i++) {
			// 获取当前sheetNum
			Sheet sheet = workbook.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			// 查询sheet一行数据都为空的行数集合
			List<Integer> queryNullLine = queryNullLine(sheet);
			// 校验数据
			int lastRowNum = sheet.getLastRowNum();
			if (!queryNullLine.isEmpty()) {
				lastRowNum = queryNullLine.get(0) - 1;
			}
			for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
				// 过滤execl数据异常行
				if (errorNumSet.contains(rowNum)) {
					continue;
				}
				Row row = sheet.getRow(rowNum);
				NurseWorkerInfoDto info = new NurseWorkerInfoDto();
				info.setCreateTime(new Date()); // 创建时间
				info.setWorkerId(IdUtils.getRandomIdByMillis()); // 护工编号
				info.setWorkerName(getCellValue(row.getCell(0))); // 护工姓名
				if (!StringUtils.isEmpty(info.getWorkerName())) {
					String pinyinAll = Chinese2PinyinUtils.toPinyinAll(info.getWorkerName());
					if (!StringUtils.isEmpty(pinyinAll) && pinyinAll.length() > 2000) {
						pinyinAll = pinyinAll.substring(0, 2000);
					}
					info.setWorkerNamePinYin(pinyinAll); // 护工姓名拼音
				}
				info.setWorkStatus(paramMap.get("workStatus").get(getCellValue(row.getCell(1))));// 工作状态
				// info.setWorkStatusName(getCellValue(row.getCell(1)));
				// 性别
				info.setSex(paramMap.get("sex").get(getCellValue(row.getCell(2))));
				// info.setSexName(getCellValue(row.getCell(2)));
				// 籍贯
				String[] nativePlace = { paramMap.get("province").get(getCellValue(row.getCell(3))) };
				info.setNativePlace(nativePlace);
				// String[] naticePlaceName = { getCellValue(row.getCell(3)) };
				// info.setNativePlaceName(naticePlaceName);
				info.setProvince(paramMap.get("province").get(getCellValue(row.getCell(3))));
				// info.setProvinceName(getCellValue(row.getCell(3)));
				// 民族
				info.setNation(paramMap.get("nation").get(getCellValue(row.getCell(4))));
				// info.setNationName(getCellValue(row.getCell(4)));
				// 手机号
				info.setPhone(getCellValue(row.getCell(5)));
				// 身份证号
				if (!StringUtils.isEmpty(getCellValue(row.getCell(6)))) {
					if (idCardMap.containsKey(getCellValue(row.getCell(6)).toLowerCase())) {
						Integer count = idCardMap.get(getCellValue(row.getCell(6)).toLowerCase()) + 1;
						idCardMap.put(getCellValue(row.getCell(6)).toLowerCase(), count);
					} else {
						idCardMap.put(getCellValue(row.getCell(6)).toLowerCase(), 1);
					}
				}
				info.setIdCard(getCellValue(row.getCell(6)));
				DateFormat dateFormat = new SimpleDateFormat("yyyy.MM.dd");
				if (!StringUtils.isEmpty(getCellValue(row.getCell(7)))) {
					try {
						// 出生日期
						info.setBirthDate(dateFormat.parse(getCellValue(row.getCell(7))));
						// 年龄
						int age = (int) (TimeUtils.getYear(new Date()) - TimeUtils.getYear(info.getBirthDate()));
						info.setAge(age < 0 ? 0 : age);
						// 入职日期
						info.setHireDate(dateFormat.parse(getCellValue(row.getCell(8))));
					} catch (ParseException e) {
					}
				}
				// 学历
				info.setEducation(paramMap.get("education").get(getCellValue(row.getCell(9))));
				// info.setEducationName(getCellValue(row.getCell(9)));
				// // 设置是否签署居间协议默认值
				// info.setHaveIntermediaryAgreement(false);
				// 设置健康证默认值
				info.setHaveHealthCert(true);
				// 设置保险状态
				info.setAccidentInsurance(false);
				// 设置工作年限
				info.setServiceYears(0);
				// 设置已签署协议
				info.setAgreements(new String[] {});
				// 设置已领取装备
				info.setEquipments(new String[] {});
				info.setEquipmentNames(new String[] {});
				// 设置信用状态
				// info.setCreditTypeName("正常");
				info.setCreditType(paramMap.get("creditType").get("正常"));
				// 设置护理证
				info.setNursingCert(false);
				// 设置责任险
				info.setLiabilityInsurance(false);
				// 设置社保
				info.setSocialSecurity(false);
				// 设置默认提醒状态
				info.setRemindStatus(false);
				// 身高
				info.setHeight(null);
				// 体重
				info.setWeight(null);
				info.setHospital(teamId);
				// 开户行
				info.setBank(paramMap.get("bank").get(getCellValue(row.getCell(10))));
				// 卡号
				info.setBankCard(getCellValue(row.getCell(11)));
				// 支行名称
				info.setBankFullName(getCellValue(row.getCell(12)));
				// 护工类型
				info.setWorkerType(WorkerTypeEnum._1.getKey());
				nurseWorkerInfoList.add(info);
			}
		}
	}

	/**
	 * 重新设置路径
	 * 
	 * @param info
	 */
	private void setUrl(NurseWorkerInfoDto info) {
		String avatarPhotoUrl = info.getAvatarPhotoUrl();
		String bankCardPhotoUrl = info.getBankCardPhotoUrl();
		String educationPhotoUrl = info.getEducationPhotoUrl();
		String healthCertPhotoUrl = info.getHealthCertPhotoUrl();
		String idCardPhotoBackUrl = info.getIdCardBackPhotoUrl();
		String idCardPhotoFaceUrl = info.getIdCardFacePhotoUrl();
		if (!StringUtils.isEmpty(avatarPhotoUrl)) {
			info.setAvatarPhotoUrl(serviceAddress + avatarPhotoUrl);
		}
		if (!StringUtils.isEmpty(bankCardPhotoUrl)) {
			info.setBankCardPhotoUrl(serviceAddress + bankCardPhotoUrl);
		}
		if (!StringUtils.isEmpty(educationPhotoUrl)) {
			info.setEducationPhotoUrl(serviceAddress + educationPhotoUrl);
		}
		if (!StringUtils.isEmpty(healthCertPhotoUrl)) {
			info.setHealthCertPhotoUrl(serviceAddress + healthCertPhotoUrl);
		}
		if (!StringUtils.isEmpty(idCardPhotoBackUrl)) {
			info.setIdCardBackPhotoUrl(serviceAddress + idCardPhotoBackUrl);
		}
		if (!StringUtils.isEmpty(idCardPhotoFaceUrl)) {
			info.setIdCardFacePhotoUrl(serviceAddress + idCardPhotoFaceUrl);
		}
	}

	/**
	 * 新建更新时处理存入数据库的URL地址
	 */
	private void dealPicturUrl(NurseWorkerDetailInfo info) {
		String extName = ""; // 文件后缀
		// 0:头像，1:身份证正面，2：身份证反面，3：学位证，4：健康证，5：银行卡
		Map<Integer, String> imgTypeMap = new HashMap<Integer, String>();
		imgTypeMap.put(0, IdUtils.getRandomIdByDateTime() + "_head");
		imgTypeMap.put(1, IdUtils.getRandomIdByDateTime() + "_face");
		imgTypeMap.put(2, IdUtils.getRandomIdByDateTime() + "_back");
		imgTypeMap.put(3, IdUtils.getRandomIdByDateTime() + "_educ");
		imgTypeMap.put(4, IdUtils.getRandomIdByDateTime() + "_heal");
		imgTypeMap.put(5, IdUtils.getRandomIdByDateTime() + "_bank");

		String avatarPhotoUrl = info.getAvatarPhotoUrl(); // 头像地址
		String bankCardPhotoUrl = info.getBankCardPhotoUrl(); // 银行卡地址
		String educationPhotoUrl = info.getEducationPhotoUrl(); // 学历证地址
		String healthCertPhotoUrl = info.getHealthCertPhotoUrl(); // 健康证地址
		String idCardPhotoFaceUrl = info.getIdCardFacePhotoUrl(); // 身份证正面地址
		String idCardPhotoBackUrl = info.getIdCardBackPhotoUrl(); // 身份证反面地址

		if (!StringUtils.isEmpty(avatarPhotoUrl)) {
			String name = avatarPhotoUrl;
			extName = name.substring(name.lastIndexOf(".")); // 文件后缀 .png
			String newName = imgTypeMap.get(0) + extName; // 文件名称 TX_WZX_0001.png
			String oldName = avatarPhotoUrl.replaceAll(serviceAddress, "");
			// 图片重命名
			savePicture(oldName, newName, extName, 0);
			info.setAvatarPhotoUrl(oldName);
		}
		if (!StringUtils.isEmpty(bankCardPhotoUrl)) {
			String name = bankCardPhotoUrl;
			extName = name.substring(name.lastIndexOf("."));
			String newName = imgTypeMap.get(5) + extName; // 文件名称 TX_WZX_0001.png
			String oldName = bankCardPhotoUrl.replaceAll(serviceAddress, "");
			// 图片重命名
			savePicture(oldName, newName, extName, 5);
			info.setBankCardPhotoUrl(oldName);
		}
		if (!StringUtils.isEmpty(educationPhotoUrl)) {
			String name = educationPhotoUrl;
			extName = name.substring(name.lastIndexOf("."));
			String newName = imgTypeMap.get(3) + extName; // 文件名称 TX_WZX_0001.png
			String oldName = educationPhotoUrl.replaceAll(serviceAddress, "");
			// 图片重命名
			savePicture(oldName, newName, extName, 3);
			info.setEducationPhotoUrl(oldName);
		}
		if (!StringUtils.isEmpty(healthCertPhotoUrl)) {
			String name = healthCertPhotoUrl;
			extName = name.substring(name.lastIndexOf("."));
			String newName = imgTypeMap.get(4) + extName; // 文件名称 TX_WZX_0001.png
			String oldName = healthCertPhotoUrl.replaceAll(serviceAddress, "");
			// 图片重命名
			savePicture(oldName, newName, extName, 4);
			info.setHealthCertPhotoUrl(oldName);
		}
		if (!StringUtils.isEmpty(idCardPhotoBackUrl)) {
			String name = idCardPhotoBackUrl;
			extName = name.substring(name.lastIndexOf("."));
			String newName = imgTypeMap.get(2) + extName; // 文件名称 TX_WZX_0001.png
			String oldName = idCardPhotoBackUrl.replaceAll(serviceAddress, "");
			// 图片重命名
			savePicture(oldName, newName, extName, 2);
			info.setIdCardBackPhotoUrl(oldName);
		}
		if (!StringUtils.isEmpty(idCardPhotoFaceUrl)) {
			String name = idCardPhotoFaceUrl;
			extName = name.substring(name.lastIndexOf("."));
			String newName = imgTypeMap.get(1) + extName; // 文件名称 TX_WZX_0001.png
			String oldName = idCardPhotoFaceUrl.replaceAll(serviceAddress, "");
			// 图片重命名
			savePicture(oldName, newName, extName, 1);
			info.setIdCardFacePhotoUrl(oldName);
		}
	}

	/**
	 * 重命名图片以及调整图片尺寸
	 * 
	 * @param oldName
	 * @param newName
	 * @param extName
	 * @type 0:头像，1:身份证正面，2：身份证反面，3：学位证(资质证件)，4：健康证，5：银行卡
	 */
	private void savePicture(String oldName, String newName, String extName, int type) {
//		File newFile = new File(PICTUREPATH + newName);
//		File dealNewFile = new File(PDFPICTUREPATH + newName);
//		if (newFile.exists() && !oldName.equals(newName)) {
//			newFile.delete();
//		}
//		if (dealNewFile.exists() && !oldName.equals(newName)) {
//			dealNewFile.delete();
//		}
//		new File(PICTUREPATH+oldName).renameTo(newFile);
		if (type == 0) {
			int width = NurseImageSizeEnum.HEAD.getWidth();
			int height = NurseImageSizeEnum.HEAD.getHeight();
			try {
				// 重调图片尺寸
				ImageUtil.resize(PICTUREPATH + oldName, PDFPICTUREPATH + oldName, width, height);
			} catch (Exception e) {
			}
		} else if (type == 1) {
			int width = NurseImageSizeEnum.FACE.getWidth();
			int height = NurseImageSizeEnum.FACE.getHeight();
			try {
				// 重调图片尺寸
				ImageUtil.resize(PICTUREPATH + oldName, PDFPICTUREPATH + oldName, width, height);
			} catch (Exception e) {
			}
		} else if (type == 3) {
			int width = NurseImageSizeEnum.EDUC.getWidth();
			int height = NurseImageSizeEnum.EDUC.getHeight();
			try {
				// 重调图片尺寸
				ImageUtil.resize(PICTUREPATH + oldName, PDFPICTUREPATH + oldName, width, height);
			} catch (Exception e) {
			}
		} else if (type == 4) {
			int width = NurseImageSizeEnum.HEAL.getWidth();
			int height = NurseImageSizeEnum.HEAL.getHeight();
			try {
				// 重调图片尺寸
				ImageUtil.resize(PICTUREPATH + oldName, PDFPICTUREPATH + oldName, width, height);
			} catch (Exception e) {
			}
		}
	}

	/**
	 * 判断是否修改健康证到期日
	 * 
	 * @param info               数据库中护工信息
	 * @param nurseWorkerInfoDto 当前护工信息
	 * @return true修改 false未修改
	 */
	private boolean isModifyHealthCertExpiryDate(NurseWorkerDetailInfo info, NurseWorkerInfoDto nurseWorkerInfoDto) {
		boolean flag = false;
		// 判断是否修改健康证到期日
		if (StringUtils.isEmpty(info.getHealthCertExpiryDate())
				&& !StringUtils.isEmpty(nurseWorkerInfoDto.getHealthCertExpiryDate())) {
			flag = true;
		} else if (!StringUtils.isEmpty(info.getHealthCertExpiryDate())
				&& StringUtils.isEmpty(nurseWorkerInfoDto.getHealthCertExpiryDate())) {
			flag = true;
		} else if (!StringUtils.isEmpty(info.getHealthCertExpiryDate())
				&& !StringUtils.isEmpty(nurseWorkerInfoDto.getHealthCertExpiryDate())) {
			if (info.getHealthCertExpiryDate().getTime() != nurseWorkerInfoDto.getHealthCertExpiryDate().getTime()) {
				flag = true;
			}
		}
		return flag;
	}

}
